package com.tansun.easycare.rule.ml.algorithm.entity;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * 数据库连接,导入
 * @author xch
 * @version 2018-08-24
 */
public class DatabaseConn {
	
	private String schema;	//schema
	private String tableName;//过滤表明
	private String dbtype;	//数据库类型
	private String dbhost; //主机host
	private int dbport;	//端口
	private String dbsid;	//数据库实例
	private String username;	//用户名
	private String password;	//密码
	private static final String oracleDriver = "oracle.jdbc.driver.OracleDriver"; //Oracle驱动
	private static final String mysqlDriver = "com.mysql.jdbc.Driver";	//mysql驱动
	private static final String postgresqlDriver = "org.postgresql.Driver";	//postgresql驱动

	private Connection conn = null;//数据库连接对象
	
	public DatabaseConn() {
	}

	public String getSchema() {
		return schema;
	}

	public void setSchema(String schema) {
		this.schema = schema;
	}

	public String getTableName() {
		return tableName;
	}

	public void setTableName(String tableName) {
		this.tableName = tableName;
	}

	public String getDbtype() {
		return dbtype;
	}

	public void setDbtype(String dbtype) {
		this.dbtype = dbtype;
	}

	public String getDbhost() {
		return dbhost;
	}

	public void setDbhost(String dbhost) {
		this.dbhost = dbhost;
	}

	public int getDbport() {
		return dbport;
	}

	public void setDbport(int dbport) {
		this.dbport = dbport;
	}

	public String getDbsid() {
		return dbsid;
	}

	public void setDbsid(String dbsid) {
		this.dbsid = dbsid;
	}

	public String getUsername() {
		return username;
	}

	public void setUsername(String username) {
		this.username = username;
	}

	public String getPassword() {
		return password;
	}

	public void setPassword(String password) {
		this.password = password;
	}

	private synchronized Connection getConn(){
		
        if(conn == null){
        	String url = null;
        	String dirver = null;
    		if ("oracle".equals(this.dbtype)){
    			url = "jdbc:oracle:thin:@//"+this.dbhost+":"+this.dbport+"/"+this.dbsid;
    			dirver = oracleDriver;
    		}else if("mysql".equals(this.dbtype)){
    			url = "jdbc:mysql://"+this.dbhost+":"+this.dbport+"/"+this.dbsid;
    			dirver = mysqlDriver;
    		}else if("postgresql".equals(this.dbtype)){
    			url = "jdbc:postgresql://"+this.dbhost+":"+this.dbport+"/"+this.dbsid;
    			dirver = postgresqlDriver;
    		}else{
    			throw new RuntimeException("请选择正确的数据库类型");
    		}
    		try {
                Class.forName(dirver);
                conn = DriverManager.getConnection(url, username, password);
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            }catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return conn;
    }
	 public List<String> queryTabels() {
		//查询表名
		List<String> tabelList = new ArrayList<String>();
		Connection conn = getConn();
		PreparedStatement pstmt = null;
		try {
			if ("oracle".equals(this.dbtype)){
				String querySql="select table_name from user_tables where 1=1 ";
				if(this.tableName.trim().length()>0){
					querySql += "and table_name like '%"+this.tableName+"%'";
				}
				pstmt = conn.prepareStatement(querySql);
			}else if("mysql".equals(this.dbtype)){
				String querySql="select table_name from information_schema.TABLES where TABLE_SCHEMA=? ";
				if(this.tableName.trim().length()>0){
					querySql += "and table_name like '%"+this.tableName+"%'";
				}
				System.out.println(querySql);
				pstmt = conn.prepareStatement(querySql);
				pstmt.setString(1, this.dbsid);
			}else{
				String querySql="select tablename as table_name from pg_tables where schemaname=? and tableowner=? ";
				if(this.tableName.trim().length()>0){
					querySql += "and table_name like '%"+this.tableName+"%'";
				}
				pstmt = conn.prepareStatement(querySql);
				pstmt.setString(1, this.schema);
				pstmt.setString(2, this.username);
			}
			ResultSet rs = pstmt.executeQuery();
			while(rs.next()){
	            // 通过字段检索
	            tabelList.add(rs.getString("table_name"));
	        }
			pstmt.close();
			conn.close();
		} catch (SQLException e) {
		    e.printStackTrace();
		}
		return tabelList;
		
	}
	//关闭连接
    public void close(){
        try {
            getConn().close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

	@Override
	public String toString() {
		return "DatabaseConn [schema=" + schema + ", tableName=" + tableName + ", dbtype=" + dbtype + ", dbhost="
				+ dbhost + ", dbport=" + dbport + ", dbsid=" + dbsid + ", username=" + username + ", password="
				+ password + "]";
	}
	
}
